package com.zretc.emptraining.daoimpl;

import java.time.LocalDateTime;

import com.zretc.emptraining.dao.PerCourseDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.CoursePlan;
import com.zretc.emptraining.entity.PerCourse;

public class PerCourseDaoImp implements PerCourseDao {

	@Override
	public boolean addPerCourse(PerCourse perCourse) {
		System.out.println(perCourse.getEmpNo()+","+perCourse.getCpNo());
		if (null != perCourse) {
			String sql = "insert into percourse values (0,?,?,now(),null,?)";
			int n = DBTools.getUpdate(sql, perCourse.getEmpNo(), perCourse.getCpNo(), "0");
			return n > 0;
		}
		return false;
	}
	
	/**
	 * 在申请未通过的时候进行取消课程申请
	 */
	@Override
	public boolean delPerCourse(int perCourseNo) {
		String sql = "update percourse set applyState = ? where percourseNo =? and applyState != ?";
		int n = DBTools.getUpdate(sql, "2", perCourseNo, "1");
		return n > 0;

	}
	
	@Override
	public boolean refusePerCourse(int perCourseNo) {
		String sql = "update percourse set passTime = now(),applyState = ? where percourseNo = ?";
		int n = DBTools.getUpdate(sql,  "2", perCourseNo);
		return n > 0;
	}

	@Override
	public boolean passPerCourse(int perCourseNo) {
		LocalDateTime now = LocalDateTime.now();
		String sql = "update percourse set passTime = now(),applyState = ? where percourseNo = ?";
		int n = DBTools.getUpdate(sql,  "1", perCourseNo);
		return n > 0;
	}

	@Override
	public PageData<PerCourse> getPerCourseByempNo(int page, int pagesize, int empNo) {
		PageData<PerCourse> pd = new PageData<PerCourse>();
		if (page < 1) {
			page = 10;
		}
		if (pagesize < 1) {
			pagesize = 10;
		}
//		String sql = "select * from percourse where empNo = ?";
		String sql = "select percourse.percourseNo,percourse.empNo,(select empName from emp where emp.empNo =percourse.empNo ) as eName,percourse.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = percourse.cpNo) )as cName,percourse.applyTime,percourse.passTime,percourse.applyState FROM percourse WHERE empNo	=? order by applyTime desc";
		pd = DBTools.exQueryByPage(sql, PerCourse.class, page, pagesize, empNo);
		return pd;
	}

	@Override
	public PageData<PerCourse> getPerCourseBydeptNo(int page, int pagesize, int deptNo) {
		PageData<PerCourse> pd = new PageData<PerCourse>();
		if (page < 1) {
			page = 10;
		}
		if (pagesize < 1) {
			pagesize = 10;
		}
		//String sql = "SELECT *  FROM percourse p RIGHT JOIN (SELECT empNo as en FROM emp WHERE deptNo = ? ) e ON e.en=p.empNo ";
		String 	sql = "select p.percourseNo  ,p.empNo,(select empName from emp where empNo =p.empNo ) as eName,p.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = p.cpNo) )as cName,p.applyTime,p.passTime ,p.applyState FROM percourse p inner JOIN (SELECT empNo as en FROM emp WHERE deptNo = ? ) e ON e.en=p.empNo order by applyTime desc ";
		pd = DBTools.exQueryByPage(sql, PerCourse.class, page, pagesize, deptNo);
		return pd;
	}
	
	@Override
	public PageData<PerCourse> getPerCourse(int page, int pageSize) {
		PageData<PerCourse> pd = new PageData<PerCourse>();
		if (page < 1) {
			page = 10;
		}
		if (pageSize < 1) {
			pageSize = 10;
		}
		//String sql = "select * from percourse";
		String sql  = "select percourse.percourseNo,percourse.empNo,(select empName from emp where emp.empNo =percourse.empNo ) as eName,percourse.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = percourse.cpNo) )as cName,percourse.applyTime,percourse.passTime,percourse.applyState FROM percourse order by applyTime desc";
		pd = DBTools.exQueryByPage(sql, PerCourse.class, page, pageSize);
		return pd;
	}

	@Override
	public boolean delPerCourseByAdmin(int perCourseNo) {
		String sql = "delete from percourse where percourseNo = ?";
		int n = DBTools.getUpdate(sql, perCourseNo);
		return n>0;
	}
	
	@Override
	public boolean updatePerCourse(PerCourse perCourse) {
		String sql = "update percourse set empNo = ?,cpNo = ? where percourseNo = ?";
		int n = DBTools.getUpdate(sql, perCourse.getEmpNo(),perCourse.getCpNo(),perCourse.getPercourseNo());
		return n>0;
	}
	
	@Override
	public PageData<PerCourse> getPerCourseByCpNo(int page, int pagesize, int cpNo) {
		String sql = "select percourse.percourseNo,percourse.empNo,(select empName from emp where emp.empNo =percourse.empNo ) as eName,percourse.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = percourse.cpNo) )as cName,percourse.applyTime,percourse.passTime ,percourse.applyState FROM percourse  where cpNo = ? order by applyTime desc";
		PageData<PerCourse> pd = DBTools.exQueryByPage(sql, PerCourse.class, page, pagesize, cpNo);
		return pd;
	}
	@Override
	public PageData<PerCourse> getPerCourseBydeptNoWithEmpNo(int page, int pagesize, int deptNo, int empNo) {
		String sql = "select p.percourseNo  ,p.empNo,(select empName from emp where empNo =p.empNo ) as eName,p.cpNo,(SELECT course.courseName from course where course.courseNo =  (SELECT courseplan.courseNo from courseplan where courseplan.cpNo = p.cpNo) )as cName ,p.applyTime,p.passTime,p.applyState FROM percourse p inner JOIN (SELECT empNo as en FROM emp WHERE deptNo = ? and empNo = ?) e ON e.en=p.empNo order by applyTime desc";
		PageData<PerCourse> pd = DBTools.exQueryByPage(sql, PerCourse.class, page, pagesize, deptNo,empNo);
		return pd;
	}
	
	@Override
	public boolean wExists(int empNo, int CpNo) {
		String sql = "select count(percourseNo) as count from percourse where empNo = ? and CpNo = ?";
		PageData<PerCourse> pd = DBTools.exQueryByPage(sql, PerCourse.class, 1, 1, empNo,CpNo);
		if (pd.getData().get(0).getCount()==0) {
			return true;
		}else {
			return false;
		}
		
	}

	@Override
	public boolean wCourseCanAdd(int cpNo) {
		// TODO Auto-generated method stub
		String sql = "select  courseplan.courseStatus from courseplan where cpNo = ?";
		PageData<CoursePlan> pd = DBTools.exQueryByPage(sql, CoursePlan.class, 1, 1, cpNo);
		System.out.println(pd.getData());
		if (pd.getData().get(0).getCourseStatus()==1) {
			return true;
		}else {
		
			return false;
		}
	}
	
}
